SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

</head>

PL/SQL

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .ambox{border:1px solid #a2a9b1;border-left:10px solid #36c;background-color:#fbfbfb;box-sizing:border-box}.mw-parser-output .ambox+link+.ambox,.mw-parser-output .ambox+link+style+.ambox,.mw-parser-output .ambox+link+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+style+.ambox,.mw-parser-output .ambox+.mw-empty-elt+link+link+.ambox{margin-top:-1px}html body.mediawiki .mw-parser-output .ambox.mbox-small-left{margin:4px 1em 4px 0;overflow:hidden;width:238px;border-collapse:collapse;font-size:88%;line-height:1.25em}.mw-parser-output .ambox-speedy{border-left:10px solid #b32424;background-color:#fee7e6}.mw-parser-output .ambox-delete{border-left:10px solid #b32424}.mw-parser-output .ambox-content{border-left:10px solid #f28500}.mw-parser-output .ambox-style{border-left:10px solid #fc3}.mw-parser-output .ambox-move{border-left:10px solid #9932cc}.mw-parser-output .ambox-protection{border-left:10px solid #a2a9b1}.mw-parser-output .ambox .mbox-text{border:none;padding:0.25em 0.5em;width:100%}.mw-parser-output .ambox .mbox-image{border:none;padding:2px 0 2px 0.5em;text-align:center}.mw-parser-output .ambox .mbox-imageright{border:none;padding:2px 0.5em 2px 0;text-align:center}.mw-parser-output .ambox .mbox-empty-cell{border:none;padding:0;width:1px}.mw-parser-output .ambox .mbox-image-div{width:52px}@media(min-width:720px){.mw-parser-output .ambox{margin:0 10%}}@media print{body.ns-0 .mw-parser-output .ambox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1248332772">

/* start https://en.wikipedia.org/ */

.mw-parser-output .multiple-issues-text{width:95%;margin:0.2em 0}.mw-parser-output .multiple-issues-text>.mw-collapsible-content{margin-top:0.3em}.mw-parser-output .compact-ambox .ambox{border:none;border-collapse:collapse;background-color:transparent;margin:0 0 0 1.6em!important;padding:0!important;width:auto;display:block}body.mediawiki .mw-parser-output .compact-ambox .ambox.mbox-small-left{font-size:100%;width:auto;margin:0}.mw-parser-output .compact-ambox .ambox .mbox-text{padding:0!important;margin:0!important}.mw-parser-output .compact-ambox .ambox .mbox-text-span{display:list-item;line-height:1.5em;list-style-type:disc}body.skin-minerva .mw-parser-output .multiple-issues-text>.mw-collapsible-toggle,.mw-parser-output .compact-ambox .ambox .mbox-image,.mw-parser-output .compact-ambox .ambox .mbox-imageright,.mw-parser-output .compact-ambox .ambox .mbox-empty-cell,.mw-parser-output .compact-ambox .hide-when-compact{display:none}

/* end https://en.wikipedia.org/ */

</style>

PL/SQL (Procedural Language for SQL) is <a href="../Oracle_Corporation" title="Oracle Corporation">Oracle Corporation</a>'s <a href="../Procedural_programming" title="Procedural programming">procedural</a> <a href="../Programming_language" title="Programming language">extension</a> for <a href="../SQL" title="SQL">SQL</a> and the <a href="../Oracle_Database" title="Oracle Database">Oracle relational database</a>. PL/SQL is available in Oracle Database (since version 6 - stored PL/SQL procedures/functions/packages/triggers since version 7), <a href="../TimesTen" title="TimesTen">TimesTen in-memory database</a> (since version 11.2.1), and <a href="../IBM_Db2" title="IBM Db2">IBM Db2</a> (since version 9.7).<a href="#cite_note-1">[1]</a> Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

PL/SQL includes procedural language elements such as <a href="../Conditional_(programming)" class="mw-redirect" title="Conditional (programming)">conditions</a> and <a href="../Program_loop" class="mw-redirect" title="Program loop">loops</a>, and can handle <a href="../Exception_handling" title="Exception handling">exceptions</a> (run-time errors). It allows the declaration of constants and <a href="../Variable_(programming)" class="mw-redirect" title="Variable (programming)">variables</a>, procedures, functions, packages, types and variables of those types, and triggers. <a href="../Array_data_type" class="mw-redirect" title="Array data type">Arrays</a> are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with <a href="../Object-PL/SQL" title="Object-PL/SQL">object-orientation</a>. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.

The first public version of the PL/SQL definition<a href="#cite_note-2">[2]</a> was in 1995. It implements the ISO <a href="../SQL/PSM" title="SQL/PSM">SQL/PSM</a> standard.<a href="#cite_note-3">[3]</a>

<div class="mw-heading mw-heading2"><h2 id="PL/SQL_program_unit">PL/SQL program unit</h2></div>

The main feature of SQL (non-procedural) is also its drawback: control statements (<a href="../Conditional_(computer_programming)" title="Conditional (computer programming)">decision-making</a> or <a href="../For_loop" title="For loop">iterative control</a>) cannot be used if only SQL is to be used. PL/SQL provides the functionality of other procedural programming languages, such as decision making, iteration etc. A PL/SQL program unit is one of the following: PL/SQL anonymous block, <a href="../Procedure_(computer_science)" class="mw-redirect" title="Procedure (computer science)">procedure</a>, <a href="../Function_(computer_science)" class="mw-redirect" title="Function (computer science)">function</a>, <a href="../Modular_programming" title="Modular programming">package</a> specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is developed, compiled, and ultimately executed on the database.<a href="#cite_note-4">[4]</a>

<div class="mw-heading mw-heading3"><h3 id="PL/SQL_anonymous_block">PL/SQL anonymous block</h3></div>

The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to <a href="../Null_(SQL)" title="Null (SQL)">NULL</a> value. The optional exception-handling part is used to handle run-time errors. Only the executable part is required. A block can have a label.<a href="#cite_note-5">[5]</a>

For example:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre><<label>> -- this is optional

DECLARE

-- this section is optional

number1 NUMBER(2);

number2 number1%TYPE := 17; -- value default

text1 VARCHAR2(12) := ' Hello world ';

text2 DATE := SYSDATE; -- current date and time

BEGIN

-- this section is mandatory, must contain at least one executable statement

SELECT street_number

INTO number1

FROM address

WHERE name = 'INU';

EXCEPTION

-- this section is optional

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode));

DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm);

END;

</pre></div>

The symbol <code>:=</code> functions as an <a href="../Assignment_operator" class="mw-redirect" title="Assignment operator">assignment operator</a> to store a value in a variable.

Blocks can be nested – i.e., because a block is an executable statement, it can appear in another block wherever an executable statement is allowed. A block can be submitted to an interactive tool (such as SQL*Plus) or embedded within an Oracle Precompiler or <a href="../Oracle_Call_Interface" title="Oracle Call Interface">OCI</a> program. The interactive tool or program runs the block once. The block is not stored in the database, and for that reason, it is called an anonymous block (even if it has a label).

<div class="mw-heading mw-heading3"><h3 id="Function">Function</h3></div>

The purpose of a PL/SQL function is generally used to compute and return a single value. This returned value may be a single scalar value (such as a number, date or character string) or a single collection (such as a nested table or array). <a href="../User-defined_function" title="User-defined function">User-defined functions</a> supplement the built-in functions provided by Oracle Corporation.<a href="#cite_note-6">[6]</a>

The PL/SQL function has the form:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type

[AUTHID <CURRENT_USER | DEFINER>] <IS|AS> -- heading part

amount number; -- declaration block

BEGIN -- executable part

RETURN <return_value>;

[Exception

none]

RETURN <return_value>;

END;

</pre></div>

Pipe-lined table functions return collections<a href="#cite_note-7">[7]</a>

and take the form:

[AUTHID <CURRENT_USER | DEFINER>] [<AGGREGATE | PIPELINED>] <IS|USING>

[declaration block]

BEGIN

PIPE ROW <return type>;

RETURN;

[Exception

exception block]

RETURN;

END;

</pre></div>

A function should only use the default IN type of parameter. The only out value from the function should be the value it returns.

<div class="mw-heading mw-heading3"><h3 id="Procedure">Procedure</h3></div>

Procedures resemble functions in that they are named program units that can be invoked repeatedly. The primary difference is that functions can be used in a SQL statement whereas procedures cannot. Another difference is that the procedure can return multiple values whereas a function should only return a single value.<a href="#cite_note-8">[8]</a>

The procedure begins with a mandatory heading part to hold the procedure name and optionally the procedure parameter list. Next come the declarative, executable and exception-handling parts, as in the PL/SQL Anonymous Block. A simple procedure might look like this:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>CREATE PROCEDURE create_email_address ( -- Procedure heading part begins

name1 VARCHAR2,

name2 VARCHAR2,

company VARCHAR2,

email OUT VARCHAR2

) -- Procedure heading part ends

AS

-- Declarative part begins (optional)

error_message VARCHAR2(30) := 'Email address is too long.';

BEGIN -- Executable part begins (mandatory)

email := name1 || '.' || name2 || '@' || company;

EXCEPTION -- Exception-handling part begins (optional)

WHEN VALUE_ERROR THEN

DBMS_OUTPUT.PUT_LINE(error_message);

END create_email_address;

</pre></div>

The example above shows a standalone procedure - this type of procedure is created and stored in a database schema using the CREATE PROCEDURE statement. A procedure may also be created in a PL/SQL package - this is called a Package Procedure. A procedure created in a PL/SQL anonymous block is called a nested procedure. The standalone or package procedures, stored in the database, are referred to as "<a href="../Stored_procedure" title="Stored procedure">stored procedures</a>".

Procedures can have three types of parameters: IN, OUT and IN OUT.

<ol><li>An IN parameter is used as input only. An IN parameter is passed by reference, though it can be changed by the inactive program.</li>

<li>An OUT parameter is initially NULL. The program assigns the parameter value and that value is returned to the calling program.</li>

<li>An IN OUT parameter may or may not have an initial value. That initial value may or may not be modified by the called program. Any changes made to the parameter are returned to the calling program by default by copying but - with the NO-COPY hint - may be passed <a href="../Call_by_reference" class="mw-redirect" title="Call by reference">by reference</a>.</li></ol>

PL/SQL also supports external procedures via the Oracle database's standard <code>ext-proc</code> process.<a href="#cite_note-9">[9]</a>

<div class="mw-heading mw-heading3"><h3 id="Package">Package</h3></div>

Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors, etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification.

Two advantages of packages are:<a href="#cite_note-10">[10]</a>

<ol><li>Modular approach, encapsulation/hiding of <a href="../Business_logic" title="Business logic">business logic</a>, security, performance improvement, re-usability. They support <a href="../Object-oriented_programming" title="Object-oriented programming">object-oriented programming</a> features like <a href="../Function_overloading" title="Function overloading">function overloading</a> and encapsulation.</li>

<li>Using package variables one can declare session level (scoped) variables since variables declared in the package specification have a session scope.</li></ol>

<div class="mw-heading mw-heading3"><h3 id="Trigger">Trigger</h3></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .hatnote{font-style:italic}.mw-parser-output div.hatnote{padding-left:1.6em;margin-bottom:0.5em}.mw-parser-output .hatnote i{font-style:normal}.mw-parser-output .hatnote+link+.hatnote{margin-top:-0.5em}@media print{body.ns-0 .mw-parser-output .hatnote{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><div role="note" class="hatnote navigation-not-searchable">Main article: <a href="../Database_trigger" title="Database trigger">Database trigger</a></div>

A <a href="../Database_trigger" title="Database trigger">database trigger</a> is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs. It is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes it—that is, the trigger fires—whenever its triggering event occurs. While a trigger is disabled, it does not fire.

You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements, and the item they act on. The trigger is said to be created on or defined on the item—which is either a table, a <a href="../View_(database)" class="mw-redirect" title="View (database)">view</a>, a schema, or the database. You also specify the timing point, which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects.

If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger. If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger.

An INSTEAD OF trigger is either: A DML trigger created on a view or a system trigger defined on a CREATE statement. The database fires the INSTEAD OF trigger instead of running the triggering statement.

<div class="mw-heading mw-heading4"><h4 id="Purpose_of_triggers">Purpose of triggers</h4></div>

Triggers can be written for the following purposes:

<ul><li>Generating some derived column values automatically</li>

<li>Enforcing referential integrity</li>

<li>Event logging and storing information on table access</li>

<li>Auditing</li>

<li>Synchronous replication of tables</li>

<li>Imposing security authorizations</li>

<li>Preventing invalid transactions</li></ul>

<div class="mw-heading mw-heading2"><h2 id="Data_types">Data types</h2></div>

The major <a href="../Datatype" class="mw-redirect" title="Datatype">datatypes</a> in PL/SQL include NUMBER, CHAR, VARCHAR2, DATE and TIMESTAMP.

<div class="mw-heading mw-heading3"><h3 id="Numeric_variables">Numeric variables</h3></div>

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>variable_name number([P, S]) := 0;

</pre></div>

To define a numeric variable, the programmer appends the variable type NUMBER to the name definition.

To specify the (optional) precision (P) and the (optional) scale (S), one can further append these in round brackets, separated by a comma. ("Precision" in this context refers to the number of digits the variable can hold, and "scale" refers to the number of digits that can follow the decimal point.)

A selection of other data-types for numeric variables would include:

binary_float, binary_double, dec, decimal, double precision, float, integer, int, numeric, real, small-int, binary_integer.

<div class="mw-heading mw-heading3"><h3 id="Character_variables">Character variables</h3></div>

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>variable_name varchar2(20) := 'Text';

-- e.g.:

address varchar2(20) := 'lake view road';

</pre></div>

To define a character variable, the programmer normally appends the variable type VARCHAR2 to the name definition. There follows in brackets the maximum number of characters the variable can store.

Other datatypes for character variables include: varchar, char, long, raw, long raw, nchar, nchar2, clob, blob, and bfile.

<div class="mw-heading mw-heading3"><h3 id="Date_variables">Date variables</h3></div>

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>variable_name date := to_date('01-01-2005 14:20:23', 'DD-MM-YYYY hh24:mi:ss');

</pre></div>

Date variables can contain date and time. The time may be left out, but there is no way to define a variable that only contains the time. There is no DATETIME type. And there is a TIME type. But there is no TIMESTAMP type that can contain fine-grained timestamp up to millisecond or nanosecond.

The <code>TO_DATE</code> function can be used to convert strings to date values. The function converts the first quoted string into a date, using as a definition the second quoted string, for example:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre> to_date('31-12-2004', 'dd-mm-yyyy')

</pre></div>

or

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre> to_date ('31-Dec-2004', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')

</pre></div>

To convert the dates to strings one uses the function <code>TO_CHAR (date_string, format_string)</code>.

PL/SQL also supports the use of ANSI date and interval literals.<a href="#cite_note-OraDTLit-11">[11]</a> The following clause gives an 18-month range:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH

AND DATE '2004-12-30'

</pre></div>

<div class="mw-heading mw-heading3"><h3 id="Exceptions">Exceptions</h3></div>

Exceptions—errors during code execution—are of two types: user-defined and predefined.

User-defined exceptions are always raised explicitly by the programmers, using the <code>RAISE</code> or <code>RAISE_APPLICATION_ERROR</code> commands, in any situation where they determine it is impossible for normal execution to continue. The <code>RAISE</code> command has the syntax:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>RAISE <exception name>;

</pre></div>

Oracle Corporation has predefined several exceptions like <code>NO_DATA_FOUND</code>, <code>TOO_MANY_ROWS</code>, etc.

Each exception has an SQL error number and SQL error message associated with it. Programmers can access these by using the <code>SQLCODE</code> and <code>SQLERRM</code> functions.

<div class="mw-heading mw-heading3"><h3 id="Datatypes_for_specific_columns">Datatypes for specific columns</h3></div>

<pre>Variable_name Table_name.Column_name%type;

</pre>

This syntax defines a variable of the type of the referenced column on the referenced tables.

Programmers specify user-defined datatypes with the syntax:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>type data_type is record (field_1 type_1 := xyz, field_2 type_2 := xyz, ..., field_n type_n := xyz);

</pre></div>

For example:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>declare

type t_address is record (

name address.name%type,

street address.street%type,

street_number address.street_number%type,

postcode address.postcode%type);

v_address t_address;

begin

select name, street, street_number, postcode into v_address from address where rownum = 1;

end;

</pre></div>

This sample program defines its own datatype, called t_address, which contains the fields name, street, street_number and postcode.

So according to the example, we are able to copy the data from the database to the fields in the program.

Using this datatype the programmer has defined a variable called v_address and loaded it with data from the ADDRESS table.

Programmers can address individual attributes in such a structure by means of the dot-notation, thus:

<pre>v_address.street := 'High Street';

</pre>

<div class="mw-heading mw-heading2"><h2 id="Conditional_statements">Conditional statements</h2></div>

The following code segment shows the IF-THEN-ELSIF-ELSE construct. The ELSIF and ELSE parts are optional so it is possible to create simpler IF-THEN or, IF-THEN-ELSE constructs.

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>IF x = 1 THEN

sequence_of_statements_1;

ELSIF x = 2 THEN

sequence_of_statements_2;

ELSIF x = 3 THEN

sequence_of_statements_3;

ELSIF x = 4 THEN

sequence_of_statements_4;

ELSIF x = 5 THEN

sequence_of_statements_5;

ELSE

sequence_of_statements_N;

END IF;

</pre></div>

The CASE statement simplifies some large IF-THEN-ELSIF-ELSE structures.

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>CASE

WHEN x = 1 THEN sequence_of_statements_1;

WHEN x = 2 THEN sequence_of_statements_2;

WHEN x = 3 THEN sequence_of_statements_3;

WHEN x = 4 THEN sequence_of_statements_4;

WHEN x = 5 THEN sequence_of_statements_5;

ELSE sequence_of_statements_N;

END CASE;

</pre></div>

CASE statement can be used with predefined selector:

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>CASE x

WHEN 1 THEN sequence_of_statements_1;

WHEN 2 THEN sequence_of_statements_2;

WHEN 3 THEN sequence_of_statements_3;

WHEN 4 THEN sequence_of_statements_4;

WHEN 5 THEN sequence_of_statements_5;

ELSE sequence_of_statements_N;

END CASE;

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="Array_handling">Array handling</h2></div>

PL/SQL refers to <a href="../Array_data_type" class="mw-redirect" title="Array data type">arrays</a> as "collections". The language offers three types of collections:

<ol><li><a href="../Associative_array" title="Associative array">Associative arrays</a> (Index-by tables)</li>

<li>Nested tables</li>

<li>Varrays (variable-size arrays)</li></ol>

Programmers must specify an upper limit for varrays, but need not for index-by tables or for nested tables. The language includes several collection <a href="../Method_(computer_science)" class="mw-redirect" title="Method (computer science)">methods</a> used to manipulate collection elements: for example FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE, etc. Index-by tables can be used to simulate associative arrays, as in this <a href="../Ackermann_function#cite_note-10" title="Ackermann function">example of a memo function for Ackermann's function in PL/SQL</a>.

<div class="mw-heading mw-heading3"><h3 id="Associative_arrays_(index-by_tables)">Associative arrays (index-by tables)</h3></div>

With index-by tables, the array can be indexed by numbers or strings. It parallels a <a href="../Java_(programming_language)" title="Java (programming language)">Java</a> map, which comprises key-value pairs. There is only one dimension and it is unbounded.

<div class="mw-heading mw-heading3"><h3 id="Nested_tables">Nested tables</h3></div>

With <a href="../Nested_SQL" title="Nested SQL">nested tables</a> the programmer needs to understand what is nested. Here, a new type is created that may be composed of a number of components. That type can then be used to make a column in a table, and nested within that column are those components.

<div class="mw-heading mw-heading3"><h3 id="Varrays_(variable-size_arrays)">Varrays (variable-size arrays)</h3></div>

With Varrays you need to understand that the word "variable" in the phrase "variable-size arrays" doesn't apply to the size of the array in the way you might think that it would. The size the array is declared with is in fact fixed. The number of elements in the array is variable up to the declared size. Arguably then, variable-sized arrays aren't that variable in size.

<div class="mw-heading mw-heading2"><h2 id="Cursors">Cursors</h2></div>

A <a href="../Cursor_(databases)" title="Cursor (databases)">cursor</a> is a pointer to a private SQL area that stores information coming from a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). A <a href="../Cursor_(databases)" title="Cursor (databases)">cursor</a> holds the rows (one or more) returned by a SQL statement.

The set of rows the <a href="../Cursor_(databases)" title="Cursor (databases)">cursor</a> holds is referred to as the active set.<a href="#cite_note-12">[12]</a>

A <a href="../Cursor_(databases)" title="Cursor (databases)">cursor</a> can be explicit or implicit. In a FOR loop, an explicit cursor shall be used if the query will be reused, otherwise an implicit cursor is preferred. If using a cursor inside a loop, use a FETCH is recommended when needing to bulk collect or when needing dynamic SQL.

<div class="mw-heading mw-heading2"><h2 id="Looping">Looping</h2></div>

As a procedural language by definition, PL/SQL provides several <a href="../Iteration" title="Iteration">iteration</a> constructs, including basic LOOP statements, <a href="../While_loop" title="While loop">WHILE loops</a>, <a href="../For_loop" title="For loop">FOR loops</a>, and Cursor FOR loops. Since Oracle 7.3 the REF CURSOR type was introduced to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types.

<div class="mw-heading mw-heading3"><h3 id="LOOP_statements">LOOP statements</h3></div>

LOOP

statements

<<child_loop>>

loop

statements

exit parent_loop when <condition>; -- Terminates both loops

exit when <condition>; -- Returns control to parent_loop

end loop child_loop;

if <condition> then

continue; -- continue to next iteration

end if;

END LOOP parent_loop;

</pre></div><a href="#cite_note-13">[13]</a>

Loops can be terminated by using the <code>EXIT</code> <a href="../Keyword_(computer_programming)" class="mw-redirect" title="Keyword (computer programming)">keyword</a>, or by raising an <a href="../Exception_handling" title="Exception handling">exception</a>.

<div class="mw-heading mw-heading3"><h3 id="FOR_loops">FOR loops</h3></div>

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>DECLARE

var NUMBER;

BEGIN

/* N.B. for loop variables in PL/SQL are new declarations, with scope only inside the loop */

FOR var IN 0 .. 10 LOOP

DBMS_OUTPUT.PUT_LINE(var);

END LOOP;

IF var IS NULL THEN

DBMS_OUTPUT.PUT_LINE('var is null');

ELSE

END IF;

END;

</pre></div>

Output:

<pre> 0

var is null

</pre>

<div class="mw-heading mw-heading3"><h3 id="Cursor_FOR_loops">Cursor FOR loops</h3></div>

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>FOR RecordIndex IN (SELECT person_code FROM people_table)

LOOP

DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);

END LOOP;

</pre></div>

Cursor-for loops automatically open a <a href="../Cursor_(databases)" title="Cursor (databases)">cursor</a>, read in their data and close the cursor again.

As an alternative, the PL/SQL programmer can pre-define the cursor's SELECT-statement in advance to (for example) allow re-use or make the code more understandable (especially useful in the case of long or complex queries).

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>DECLARE

CURSOR cursor_person IS

SELECT person_code FROM people_table;

BEGIN

FOR RecordIndex IN cursor_person

LOOP

DBMS_OUTPUT.PUT_LINE(recordIndex.person_code);

END LOOP;

END;

</pre></div>

The concept of the person_code within the FOR-loop gets expressed with dot-notation ("."):

<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>RecordIndex.person_code

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="Dynamic_SQL">Dynamic SQL</h2></div>

While programmers can readily embed <a href="../Data_Manipulation_Language" class="mw-redirect" title="Data Manipulation Language">Data Manipulation Language</a> (DML) statements directly into PL/SQL code using straightforward SQL statements, <a href="../Data_Definition_Language" class="mw-redirect" title="Data Definition Language">Data Definition Language</a> (DDL) requires more complex "Dynamic SQL" statements in the PL/SQL code. However, DML statements underpin the majority of PL/SQL code in typical software applications.

In the case of PL/SQL dynamic SQL, early versions of the Oracle Database required the use of a complicated Oracle <code>DBMS_SQL</code> package library. More recent versions have however introduced a simpler "Native Dynamic SQL", along with an associated <code>EXECUTE IMMEDIATE</code> syntax.

<div class="mw-heading mw-heading2"><h2 id="Similar_languages">Similar languages</h2></div>

PL/SQL works analogously to the embedded procedural languages associated with other <a href="../Relational_database" title="Relational database">relational databases</a>. For example, <a href="../Sybase" title="Sybase">Sybase</a> <a href="../Adaptive_Server_Enterprise" title="Adaptive Server Enterprise">ASE</a> and <a href="../Microsoft" title="Microsoft">Microsoft</a> <a href="../Microsoft_SQL_Server" title="Microsoft SQL Server">SQL Server</a> have <a href="../Transact-SQL" title="Transact-SQL">Transact-SQL</a>, <a href="../PostgreSQL" title="PostgreSQL">PostgreSQL</a> has <a href="../PL/pgSQL" title="PL/pgSQL">PL/pgSQL</a> (which emulates PL/SQL to an extent), <a href="../MariaDB" title="MariaDB">MariaDB</a> includes a PL/SQL compatibility parser,<a href="#cite_note-14">[14]</a> and <a href="../IBM_Db2" title="IBM Db2">IBM Db2</a> includes SQL Procedural Language,<a href="#cite_note-15">[15]</a>

which conforms to the <a href="../SQL" title="SQL">ISO SQL</a>’s <a href="../SQL/PSM" title="SQL/PSM">SQL/PSM</a> standard.

The designers of PL/SQL modeled its syntax on that of <a href="../Ada_programming_language" class="mw-redirect" title="Ada programming language">Ada</a>. Both Ada and PL/SQL have <a href="../Pascal_programming_language" class="mw-redirect" title="Pascal programming language">Pascal</a> as a common ancestor, and so PL/SQL also resembles Pascal in most aspects. However, the structure of a PL/SQL package does not resemble the basic <a href="../Object_Pascal" title="Object Pascal">Object Pascal</a> program structure as implemented by a <a href="../Borland_Delphi" class="mw-redirect" title="Borland Delphi">Borland Delphi</a> or <a href="../Free_Pascal" title="Free Pascal">Free Pascal</a> unit. Programmers can define public and private global data-types, constants, and static variables in a PL/SQL package.<a href="#cite_note-16">[16]</a>

PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usage in <a href="../Object-oriented_programming" title="Object-oriented programming">object-oriented programming</a> languages like <a href="../Object_Pascal" title="Object Pascal">Object Pascal</a>, <a href="../C%2B%2B" title="C++">C++</a> and <a href="../Java_(programming_language)" title="Java (programming language)">Java</a>. PL/SQL refers to a class as an "Abstract Data Type" (ADT) or "User Defined Type" (UDT), and defines it as an <a href="../Oracle_database" class="mw-redirect" title="Oracle database">Oracle</a> SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the <a href="../Oracle_database" class="mw-redirect" title="Oracle database">Oracle</a> SQL Engine and the <a href="../Oracle_database" class="mw-redirect" title="Oracle database">Oracle</a> PL/SQL engine. The constructor and methods of an Abstract Data Type are written in PL/SQL. The resulting Abstract Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables.

PL/SQL is fundamentally distinct from <a href="../Transact-SQL" title="Transact-SQL">Transact-SQL</a>, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages,<a href="#cite_note-17">[17]</a> but also due to the very significant differences in the way Oracle and SQL Server deal with <a href="../Concurrency_control" title="Concurrency control">concurrency</a> and <a href="../Lock_(computer_science)" title="Lock (computer science)">locking</a>.

The StepSqlite product is a PL/SQL compiler for the popular small database <a href="../SQLite" title="SQLite">SQLite</a> which supports a subset of PL/SQL syntax. Oracle's <a href="../Berkeley_DB" title="Berkeley DB">Berkeley DB</a> 11g R2 release added support for <a href="../SQL" title="SQL">SQL</a> based on the popular SQLite API by including a version of SQLite in Berkeley DB.<a href="#cite_note-18">[18]</a> Consequently, StepSqlite can also be used as a third-party tool to run PL/SQL code on Berkeley DB.<a href="#cite_note-19">[19]</a>

<li>PL/SQL editors</li>

<li><a href="../Relational_database_management_system" class="mw-redirect" title="Relational database management system">Relational database management systems</a></li></ul>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("../mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("../mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("../mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("../mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite id="CITEREFSerge_Rielau_(srielau@ca.ibm.com),_SQL_Architect,_STSM,_IBM" class="citation web cs1">Serge Rielau (srielau@ca.ibm.com), SQL Architect, STSM, IBM. <a rel="nofollow" class="external text" href="http://www.ibm.com/developerworks/data/library/techarticle/dm-0907oracleappsondb2/index.html">"DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows"</a>. Ibm.com. Retrieved 2012-07-26.</cite><code class="cs1-code">{{cite web}}</code>: CS1 maint: multiple names: authors list (link)

</li>

<li id="cite_note-2"><a href="#cite_ref-2">^</a> Steven Feuerstein (1995), "Oracle PL/SQL Programming", 1st, First Edition.

</li>

<li id="cite_note-3"><a href="#cite_ref-3">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Oracle-Compliance-with-SQLPSM2011.html#GUID-651F9066-1511-407B-A002-C04AB2F2A534">"Oracle Compliance with SQL/PSM"</a>.</cite>

</li>

<li id="cite_note-4"><a href="#cite_ref-4">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a88856/c02servr.htm">"Oracle Server Features"</a>. Oracle. Retrieved 2020-09-21.</cite>

</li>

<li id="cite_note-5"><a href="#cite_ref-5">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://livesql.oracle.com/apex/livesql/file/tutorial_KS0KNKP218J86THKN85XU37.html">"PL/SQL Anonymous Blocks"</a>. Oracle. Retrieved 2020-09-21.</cite>

</li>

<li id="cite_note-6"><a href="#cite_ref-6">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm">"Database SQL Reference"</a>. Oracle. Retrieved 2020-09-21.</cite>

</li>

<cite id="CITEREFNandaFeuerstein2005" class="citation book cs1">Nanda, Arup; Feuerstein, Steven (2005). <a rel="nofollow" class="external text" href="https://books.google.com/books?id=-wFDRRSxQFMC">Oracle PL/SQL for DBAs</a>. O'Reilly Series. O'Reilly Media, Inc. pp. 122, 429. <a href="../ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-0-596-00587-0</bdi>. Retrieved 2011-01-11. <q>A pipelined table function [...] returns a result set as a collection [...] iteratively. [... A]s each row is ready to be assigned to the collection, it is 'piped out' of the function.</q></cite>

</li>

<li id="cite_note-8"><a href="#cite_ref-8">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm">"Database SQL Reference"</a>. Oracle. Retrieved 2020-09-21.</cite>

</li>

<cite id="CITEREFGupta2012" class="citation book cs1">Gupta, Saurabh K. (2012). "5: Using Advanced Interface Methods". <a rel="nofollow" class="external text" href="https://books.google.com/books?id=Hq1KDAAAQBAJ">Advanced Oracle PL/SQL Developer's Guide</a>. Professional experience distilled (2 ed.). Birmingham: Packt Publishing Ltd (published 2016). p. 143. <a href="../ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>9781785282522</bdi>. Retrieved 2017-06-08. <q>Whenever the PL/SQL runtime engine encounters an external procedure call, the Oracle Database starts the extproc process. The database passes on the information received from the call specification to the <code>extproc</code> process, which helps it to locate the external procedure within the library and execute it using the supplied parameters. The <code>extproc</code> process loads the dynamic linked library, executes the external procedure, and returns the result back to the database.</q></cite>

</li>

<li id="cite_note-10"><a href="#cite_ref-10">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://docs.oracle.com/cd/B19306_01/B14251_01/adfns_packages.htm#ADFNS009">"Coding PL/SQL Procedures and Packages"</a>. Oracle. Retrieved 2020-09-21.</cite>

</li>

<cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#sthref365">"Literals"</a>. Oracle iSQL Reference 10g Release 2 (10.2). Oracle. Retrieved 2009-03-20.</cite>

</li>

<li id="cite_note-12"><a href="#cite_ref-12">^</a> <cite id="CITEREFFeuerstein" class="citation web cs1">Feuerstein, Steven. <a rel="nofollow" class="external text" href="http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html">"Working with Cursors"</a>. oracle.com.</cite>

</li>

<li id="cite_note-13"><a href="#cite_ref-13">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/controlstructures.htm#sthref921">"Database PL/SQL User's Guide and Reference"</a>. download.oracle.com.</cite>

</li>

<li id="cite_note-14"><a href="#cite_ref-14">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://mariadb.org/wp-content/uploads/2018/02/Whats-New-in-MariaDB-10.3-Unconference.pdf">"What's New in MariaDB Server 10.3"</a> (PDF). MariaDB. Retrieved 2018-08-21.</cite>

</li>

<li id="cite_note-15"><a href="#cite_ref-15">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011916.htm">"SQL PL"</a>. Publib.boulder.ibm.com. Retrieved 2012-07-26.</cite>

</li>

<li id="cite_note-16"><a href="#cite_ref-16">^</a> Oracle documentation <a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/packages.htm#LNPLS00913">"Private and public items in PL/SQL"</a>

</li>

<li id="cite_note-17"><a href="#cite_ref-17">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm">"Migrating from Oracle to SQL Server - T-SQL, PL/SQL differences: Narayana Vyas Kondreddi's home page"</a>. vyaskn.tripod.com.</cite>

</li>

<li id="cite_note-18"><a href="#cite_ref-18">^</a> <cite id="CITEREFBurd2010" class="citation web cs1">Burd, Gregory (March 24, 2010). <a rel="nofollow" class="external text" href="https://twitter.com/gregburd/statuses/10979336891">"@humanications We didn't re-implement the SQLite API, we include a version of SQLite which uses Berkeley DB for storage (replacing btree.c)"</a>.</cite>

</li>

<li id="cite_note-19"><a href="#cite_ref-19">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="http://www.oracle.com/technetwork/database/berkeleydb/db-faq-095848.html#DoesBerkeleyDBsupportPLSQL">"Official Berkeley DB FAQ"</a>. <a href="../Oracle_Corporation" title="Oracle Corporation">Oracle Corporation</a>. Retrieved March 30, 2010. <q>Does Berkeley DB support PL/SQL?</q></cite>

</li>

</ol></div></div>

<div class="mw-heading mw-heading2"><h2 id="Further_reading">Further reading</h2></div>

<ul><li><cite id="CITEREFFeuersteinBill_Pribyl2014" class="citation book cs1"><a href="../Steven_Feuerstein" title="Steven Feuerstein">Feuerstein, Steven</a>; Bill Pribyl (2014). Oracle PL/SQL Programming (6th ed.). <a href="../O'Reilly_%26_Associates" class="mw-redirect" title="O'Reilly & Associates">O'Reilly & Associates</a>. <a href="../ISBN_(identifier)" class="mw-redirect" title="ISBN (identifier)">ISBN</a> <bdi>978-1449324452</bdi>.</cite></li>

<li><cite id="CITEREFNaudé2005" class="citation web cs1">Naudé, Frank (June 9, 2005). <a rel="nofollow" class="external text" href="http://www.orafaq.com/faqplsql.htm">"Oracle PL/SQL FAQ rev 2.08"</a>.</cite></li></ul>

<div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .side-box{margin:4px 0;box-sizing:border-box;border:1px solid #aaa;font-size:88%;line-height:1.25em;background-color:var(--background-color-interactive-subtle,#f8f9fa);display:flow-root}.mw-parser-output .infobox .side-box{font-size:100%}.mw-parser-output .side-box-abovebelow,.mw-parser-output .side-box-text{padding:0.25em 0.9em}.mw-parser-output .side-box-image{padding:2px 0 2px 0.9em;text-align:center}.mw-parser-output .side-box-imageright{padding:2px 0.9em 2px 0;text-align:center}@media(min-width:500px){.mw-parser-output .side-box-flex{display:flex;align-items:center}.mw-parser-output .side-box-text{flex:1;min-width:0}}@media(min-width:720px){.mw-parser-output .side-box{width:238px}.mw-parser-output .side-box-right{clear:right;float:right;margin-left:1em}.mw-parser-output .side-box-left{margin-right:1em}}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1237033735">

/* start https://en.wikipedia.org/ */

@media print{body.ns-0 .mw-parser-output .sistersitebox{display:none!important}}@media screen{html.skin-theme-clientpref-night .mw-parser-output .sistersitebox img[src*="Wiktionary-logo-en-v2.svg"]{background-color:white}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .sistersitebox img[src*="Wiktionary-logo-en-v2.svg"]{background-color:white}}

/* end https://en.wikipedia.org/ */

</style><div class="side-box side-box-right sistersitebox"><style data-mw-deduplicate="TemplateStyles:r1126788409">

/* start https://en.wikipedia.org/ */

.mw-parser-output .plainlist ol,.mw-parser-output .plainlist ul{line-height:inherit;list-style:none;margin:0;padding:0}.mw-parser-output .plainlist ol li,.mw-parser-output .plainlist ul li{margin-bottom:0}

/* end https://en.wikipedia.org/ */

</style>

<div class="side-box-text plainlist">The Wikibook <a href="https://en.wikibooks.org/wiki/Oracle_Programming" class="extiw external" title="wikibooks:Oracle Programming">Oracle Programming</a> has a page on the topic of: <a href="https://en.wikibooks.org/wiki/Oracle_Programming/SQL_Cheatsheet" class="extiw external" title="wikibooks:Oracle Programming/SQL Cheatsheet">SQL Cheatsheet</a></div></div>

</div>

<ul><li><a rel="nofollow" class="external text" href="http://www.orafaq.com/wiki/PL/SQL_FAQ">Oracle FAQ: PL/SQL</a></li>

<li><a rel="nofollow" class="external text" href="http://www.oracle.com/technology/tech/pl_sql/index.html">Oracle Technology Center</a></li>

<li><a rel="nofollow" class="external text" href="http://tahiti.oracle.com">Oracle Tahiti Search Engine</a></li>

<li><a rel="nofollow" class="external text" href="http://www.morganslibrary.org/library.html">Morgan's Library</a></li>

<li><a rel="nofollow" class="external text" href="http://www.oracle-base.com/articles/plsql/ArticlesPLSQL.php">ORACLE-BASE: PL/SQL Articles</a></li></ul>

/* start https://en.wikipedia.org/ */

.mw-parser-output .hlist dl,.mw-parser-output .hlist ol,.mw-parser-output .hlist ul{margin:0;padding:0}.mw-parser-output .hlist dd,.mw-parser-output .hlist dt,.mw-parser-output .hlist li{margin:0;display:inline}.mw-parser-output .hlist.inline,.mw-parser-output .hlist.inline dl,.mw-parser-output .hlist.inline ol,.mw-parser-output .hlist.inline ul,.mw-parser-output .hlist dl dl,.mw-parser-output .hlist dl ol,.mw-parser-output .hlist dl ul,.mw-parser-output .hlist ol dl,.mw-parser-output .hlist ol ol,.mw-parser-output .hlist ol ul,.mw-parser-output .hlist ul dl,.mw-parser-output .hlist ul ol,.mw-parser-output .hlist ul ul{display:inline}.mw-parser-output .hlist .mw-empty-li{display:none}.mw-parser-output .hlist dt::after{content:": "}.mw-parser-output .hlist dd::after,.mw-parser-output .hlist li::after{content:" · ";font-weight:bold}.mw-parser-output .hlist dd:last-child::after,.mw-parser-output .hlist dt:last-child::after,.mw-parser-output .hlist li:last-child::after{content:none}.mw-parser-output .hlist dd dd:first-child::before,.mw-parser-output .hlist dd dt:first-child::before,.mw-parser-output .hlist dd li:first-child::before,.mw-parser-output .hlist dt dd:first-child::before,.mw-parser-output .hlist dt dt:first-child::before,.mw-parser-output .hlist dt li:first-child::before,.mw-parser-output .hlist li dd:first-child::before,.mw-parser-output .hlist li dt:first-child::before,.mw-parser-output .hlist li li:first-child::before{content:" (";font-weight:normal}.mw-parser-output .hlist dd dd:last-child::after,.mw-parser-output .hlist dd dt:last-child::after,.mw-parser-output .hlist dd li:last-child::after,.mw-parser-output .hlist dt dd:last-child::after,.mw-parser-output .hlist dt dt:last-child::after,.mw-parser-output .hlist dt li:last-child::after,.mw-parser-output .hlist li dd:last-child::after,.mw-parser-output .hlist li dt:last-child::after,.mw-parser-output .hlist li li:last-child::after{content:")";font-weight:normal}.mw-parser-output .hlist ol{counter-reset:listitem}.mw-parser-output .hlist ol>li{counter-increment:listitem}.mw-parser-output .hlist ol>li::before{content:" "counter(listitem)"\a0 "}.mw-parser-output .hlist dd ol>li:first-child::before,.mw-parser-output .hlist dt ol>li:first-child::before,.mw-parser-output .hlist li ol>li:first-child::before{content:" ("counter(listitem)"\a0 "}

/* end https://en.wikipedia.org/ */

</style><style data-mw-deduplicate="TemplateStyles:r1236075235">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbox{box-sizing:border-box;border:1px solid #a2a9b1;width:100%;clear:both;font-size:88%;text-align:center;padding:1px;margin:1em auto 0}.mw-parser-output .navbox .navbox{margin-top:0}.mw-parser-output .navbox+.navbox,.mw-parser-output .navbox+.navbox-styles+.navbox{margin-top:-1px}.mw-parser-output .navbox-inner,.mw-parser-output .navbox-subgroup{width:100%}.mw-parser-output .navbox-group,.mw-parser-output .navbox-title,.mw-parser-output .navbox-abovebelow{padding:0.25em 1em;line-height:1.5em;text-align:center}.mw-parser-output .navbox-group{white-space:nowrap;text-align:right}.mw-parser-output .navbox,.mw-parser-output .navbox-subgroup{background-color:#fdfdfd}.mw-parser-output .navbox-list{line-height:1.5em;border-color:#fdfdfd}.mw-parser-output .navbox-list-with-group{text-align:left;border-left-width:2px;border-left-style:solid}.mw-parser-output tr+tr>.navbox-abovebelow,.mw-parser-output tr+tr>.navbox-group,.mw-parser-output tr+tr>.navbox-image,.mw-parser-output tr+tr>.navbox-list{border-top:2px solid #fdfdfd}.mw-parser-output .navbox-title{background-color:#ccf}.mw-parser-output .navbox-abovebelow,.mw-parser-output .navbox-group,.mw-parser-output .navbox-subgroup .navbox-title{background-color:#ddf}.mw-parser-output .navbox-subgroup .navbox-group,.mw-parser-output .navbox-subgroup .navbox-abovebelow{background-color:#e6e6ff}.mw-parser-output .navbox-even{background-color:#f7f7f7}.mw-parser-output .navbox-odd{background-color:transparent}.mw-parser-output .navbox .hlist td dl,.mw-parser-output .navbox .hlist td ol,.mw-parser-output .navbox .hlist td ul,.mw-parser-output .navbox td.hlist dl,.mw-parser-output .navbox td.hlist ol,.mw-parser-output .navbox td.hlist ul{padding:0.125em 0}.mw-parser-output .navbox .navbar{display:block;font-size:100%}.mw-parser-output .navbox-title .navbar{float:left;text-align:left;margin-right:0.5em}body.skin--responsive .mw-parser-output .navbox-image img{max-width:none!important}@media print{body.ns-0 .mw-parser-output .navbox{display:none!important}}

/* end https://en.wikipedia.org/ */

</style></div><div role="navigation" class="navbox" aria-labelledby="Oracle_Corporation396" style="padding:3px"><table class="nowraplinks hlist mw-collapsible mw-collapsed navbox-inner" style="border-spacing:0;background:transparent;color:inherit"><tbody><tr><th scope="col" class="navbox-title" colspan="2"><style data-mw-deduplicate="TemplateStyles:r1239400231">

/* start https://en.wikipedia.org/ */

.mw-parser-output .navbar{display:inline;font-size:88%;font-weight:normal}.mw-parser-output .navbar-collapse{float:left;text-align:left}.mw-parser-output .navbar-boxtext{word-spacing:0}.mw-parser-output .navbar ul{display:inline-block;white-space:nowrap;line-height:inherit}.mw-parser-output .navbar-brackets::before{margin-right:-0.125em;content:"[ "}.mw-parser-output .navbar-brackets::after{margin-left:-0.125em;content:" ]"}.mw-parser-output .navbar li{word-spacing:-0.125em}.mw-parser-output .navbar a>span,.mw-parser-output .navbar a>abbr{text-decoration:inherit}.mw-parser-output .navbar-mini abbr{font-variant:small-caps;border-bottom:none;text-decoration:none;cursor:inherit}.mw-parser-output .navbar-ct-full{font-size:114%;margin:0 7em}.mw-parser-output .navbar-ct-mini{font-size:114%;margin:0 4em}html.skin-theme-clientpref-night .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}@media(prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .navbar li a abbr{color:var(--color-base)!important}}@media print{.mw-parser-output .navbar{display:none!important}}

/* end https://en.wikipedia.org/ */

</style><div id="Oracle_Corporation396" style="font-size:114%;margin:0 4em"><a href="../Oracle_Corporation" title="Oracle Corporation">Oracle Corporation</a></div></th></tr><tr><th scope="row" class="navbox-group" style="width:1%">Corporate directors</th><td class="navbox-list-with-group navbox-list navbox-odd" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li>Jeffrey Berg</li>

<li>H. Raymond Bingham</li>

<li><a href="../Michael_Boskin" title="Michael Boskin">Michael Boskin</a></li>

<li><a href="../Safra_Catz" title="Safra Catz">Safra Catz</a></li>

<li><a href="../Larry_Ellison" title="Larry Ellison">Larry Ellison</a></li>

<li><a href="../H%C3%A9ctor_Garc%C3%ADa-Molina" title="Héctor García-Molina">Héctor García-Molina</a></li>

<li><a href="../Joseph_Grundfest" title="Joseph Grundfest">Joseph Grundfest</a></li>

<li><a href="../Jeffrey_O._Henley" class="mw-redirect" title="Jeffrey O. Henley">Jeffrey O. Henley</a></li>

<li>Donald L. Lucas</li>

<li>Naomi O. Seligman</li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Acquisitions (<a href="../List_of_acquisitions_by_Oracle" title="List of acquisitions by Oracle">list</a>)</th><td class="navbox-list-with-group navbox-list navbox-even" style="width:100%;padding:0"><div style="padding:0 0.25em">

<li><a href="../PeopleSoft" title="PeopleSoft">PeopleSoft</a></li>

<li><a href="../Hyperion_Solutions" title="Hyperion Solutions">Hyperion</a></li>

<li><a href="../Siebel_Systems" title="Siebel Systems">Siebel</a></li>

<li><a href="../JD_Edwards" title="JD Edwards">JD Edwards</a></li>

<li><a href="../RightNow_Technologies" title="RightNow Technologies">RightNow</a></li>

<li><a href="../Virtual_Iron" title="Virtual Iron">Virtual Iron</a></li>

<li><a href="../TimesTen" title="TimesTen">TimesTen</a></li>

<li>Sunopsis</li>

<li><a href="../NetSuite" title="NetSuite">NetSuite</a></li>

<li><a href="../Cerner" class="mw-redirect" title="Cerner">Cerner</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Databases</th><td class="navbox-list-with-group navbox-list navbox-odd" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="../Oracle_Database" title="Oracle Database">Oracle Database</a></li>

<li><a href="../MySQL" title="MySQL">MySQL</a></li>

<li><a href="../InnoDB" title="InnoDB">InnoDB</a></li>

<li><a href="../Berkeley_DB" title="Berkeley DB">Berkeley DB</a></li>

<li><a href="../TimesTen" title="TimesTen">TimesTen</a></li>

<li><a href="../Essbase" title="Essbase">Essbase</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Programming languages</th><td class="navbox-list-with-group navbox-list navbox-even" style="width:100%;padding:0"><div style="padding:0 0.25em">

</ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%"><abbr title="Integrated development environment">IDEs</abbr></th><td class="navbox-list-with-group navbox-list navbox-odd" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="../JDeveloper" title="JDeveloper">JDeveloper</a></li>

<li><a href="../Oracle_Forms" title="Oracle Forms">Forms</a></li>

<li><a href="../NetBeans" title="NetBeans">NetBeans</a></li>

<li><a href="../Oracle_SQL_Developer" title="Oracle SQL Developer">SQL Developer</a></li>

<li><a href="../Oracle_Developer_Studio" title="Oracle Developer Studio">Developer Studio</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Middleware</th><td class="navbox-list-with-group navbox-list navbox-even" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="../Oracle_Fusion_Middleware" title="Oracle Fusion Middleware">Fusion Middleware</a></li>

<li><a href="../Oracle_WebCenter" title="Oracle WebCenter">WebCenter</a></li>

<li><a href="../Oracle_SOA_Suite" title="Oracle SOA Suite">SOA Suite</a></li>

<li><a href="../Oracle_WebLogic_Server" title="Oracle WebLogic Server">WebLogic Server</a></li>

<li><a href="../Oracle_Coherence" title="Oracle Coherence">Coherence</a></li>

<li><a href="../Tuxedo_(software)" title="Tuxedo (software)">Tuxedo</a></li>

<li><a href="../GlassFish" title="GlassFish">GlassFish</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Operating systems</th><td class="navbox-list-with-group navbox-list navbox-odd" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="../Oracle_Linux" title="Oracle Linux">Oracle Linux</a></li>

<li><a href="../Oracle_Solaris" title="Oracle Solaris">Oracle Solaris</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Computer hardware</th><td class="navbox-list-with-group navbox-list navbox-even" style="width:100%;padding:0"><div style="padding:0 0.25em">

<li><a href="../SPARC" title="SPARC">SPARC</a> (<a href="../SPARC_T-Series" class="mw-redirect" title="SPARC T-Series">T-Series</a>, <a href="../SPARC_Enterprise" title="SPARC Enterprise">Enterprise</a>)</li>

<li><a href="../Storage_Technology_Corporation" class="mw-redirect" title="Storage Technology Corporation">StorageTek</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Computer appliances</th><td class="navbox-list-with-group navbox-list navbox-odd" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="../Oracle_Exadata" title="Oracle Exadata">Oracle Exadata</a></li>

<li><a href="../Oracle_Exalogic" title="Oracle Exalogic">Oracle Exalogic</a></li>

<li><a href="../Oracle_Big_Data_Appliance" title="Oracle Big Data Appliance">Big Data Appliance</a></li></ul>

</div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Education and recognition</th><td class="navbox-list-with-group navbox-list navbox-even" style="width:100%;padding:0"><div style="padding:0 0.25em">

<ul><li><a href="../Oracle_Certification_Program" title="Oracle Certification Program">Oracle Certification Program</a></li></ul>

</div></td></tr><tr><td class="navbox-abovebelow" colspan="2"><div>

<ul><li> Category</li></ul>

</div></td></tr></tbody></table></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .tooltip-dotted{border-bottom:1px dotted;cursor:help}

/* end https://en.wikipedia.org/ */

</style></div><div role="navigation" class="navbox authority-control" aria-labelledby="Authority_control_databases_frameless&#124;text-top&#124;10px&#124;alt=Edit_this_at_Wikidata&#124;link=https&#58;//www.wikidata.org/wiki/Q41574#identifiers&#124;class=noprint&#124;Edit_this_at_Wikidata1259" style="padding:3px"><table class="nowraplinks hlist mw-collapsible autocollapse navbox-inner" style="border-spacing:0;background:transparent;color:inherit"><tbody><tr><th scope="col" class="navbox-title" colspan="2"><div id="Authority_control_databases_frameless&#124;text-top&#124;10px&#124;alt=Edit_this_at_Wikidata&#124;link=https&#58;//www.wikidata.org/wiki/Q41574#identifiers&#124;class=noprint&#124;Edit_this_at_Wikidata1259" style="font-size:114%;margin:0 4em">Authority control databases </div></th></tr><tr><th scope="row" class="navbox-group" style="width:1%">National</th><td class="navbox-list-with-group navbox-list navbox-odd" style="width:100%;padding:0"><div style="padding:0 0.25em"><ul><li><a rel="nofollow" class="external text" href="https://d-nb.info/gnd/4457669-9">Germany</a></li><li><a rel="nofollow" class="external text" href="https://id.loc.gov/authorities/sh96008008">United States</a></li><li><a rel="nofollow" class="external text" href="https://catalogue.bnf.fr/ark:/12148/cb135115170">France</a></li><li><a rel="nofollow" class="external text" href="https://data.bnf.fr/ark:/12148/cb135115170">BnF data</a></li><li><a rel="nofollow" class="external text" href="https://aleph.nkp.cz/F/?func=find-c&local_base=aut&ccl_term=ica=ph249440&CON_LNG=ENG">Czech Republic</a></li><li><a rel="nofollow" class="external text" href="https://datos.bne.es/resource/XX544855">Spain</a></li><li><a rel="nofollow" class="external text" href="https://www.nli.org.il/en/authorities/987007561375705171">Israel</a></li></ul></div></td></tr><tr><th scope="row" class="navbox-group" style="width:1%">Other</th><td class="navbox-list-with-group navbox-list navbox-even" style="width:100%;padding:0"><div style="padding:0 0.25em"><ul><li><a rel="nofollow" class="external text" href="https://lux.collections.yale.edu/view/concept/8843b78b-c048-48fd-bf41-2e5d78bfbd25">Yale LUX</a></li></ul></div></td></tr></tbody></table></div></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2025-07-18" href="https://en.wikipedia.org/wiki/?title=PL%2FSQL&oldid=1301134122">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>